
	SELECT 
	 REGION_DESC_U REGION
	,MARKET_AREA_U MARKET
	,CASE WHEN INDEX(AM_U,'(') >0 THEN SUBSTR(AM_U, 1, INDEX(AM_U,'(') -2) ELSE TRIM(AM_U)  END ACCT_MGR
	,PORTFOLIO_NAME_U PORTFOLIO
	,STATE
	,TRIM(STATE)||TRIM(PROPERTY_ID_U) TRAC_ID		
	,CASE 
	 WHEN INDEX(PRODUCT_NM_U,'VOICE') GT 0 THEN 'VOIP' 
	 WHEN INDEX(PRODUCT_NM_U,'HSIA') GT 0 THEN 'HSIA' 
	 ELSE 'IPTV' 
	 END PRODUCT
	,PRODUCT_NM_U     FEATURE	        
	,CASE PRODUCT_ACTION_CD_U  
	WHEN 'I' THEN 'IN' ELSE 'OUT' END 
	DIRECTION
	,PROD_QTY_U
	 FROM
	(  -- TOT
		SELECT  
	 	CASE  STATE 
		WHEN  'IL'  THEN 'A'
		WHEN  'IN'  THEN 'A'
		WHEN  'MI'  THEN 'A'
		WHEN  'WI'  THEN 'A'
		WHEN  'OH'  THEN 'A'
		WHEN  'CA'  THEN 'P'
		WHEN  'NV'  THEN 'P'
		WHEN  'CT'  THEN 'N'
		WHEN  'TX'  THEN 'S'
		WHEN  'KS'  THEN 'S'
		WHEN  'AR'  THEN 'S'
		WHEN  'OK'  THEN 'S'
		WHEN  'MO'  THEN 'S'
		ELSE 'U'
		END  AS  REGION_U
		,STATE     /*   ADDED 9/9/09 PER MIST REQUEST  126821   */
		,WIRE_CENTER
		,BASE.PROPERTY_ID AS PROPERTY_ID_U
		,NATIONAL_IND_U
		,DWELLING_TYPE_U
		,ADDRESS  AS ADDRESS_U
		,UNIT
		,CITY_NM AS CITY_NM_U
		,REGION AS REGION_DESC_U
		,PROPERTY_NAME  AS PROPERTY_NAME_U
		,AM  AS AM_U
		,AE_ASSIGNED  AS AE_ASSIGNED_U
		,PORTFOLIO_NAME  AS PORTFOLIO_NAME_U
		,PR_STATUS  AS PR_STATUS_U
		,C_STATUS  AS C_STATUS_U
		,MARKET_AREA  AS MARKET_AREA_U
		,DISTRIBUTION_AREA AS DA_U
		,TRIM(WIRE_CENTER)||TRIM(DISTRIBUTION_AREA)  AS WCDA_U
		,BASE.EVENT_ID  AS EVENT_ID_U
		,SONBR  AS SONBR_U
		,DISC_RSN_CD_U
		,DISC_RSN_U   
		,BASE.SALES_CODE AS SALES_CODE_U
		,COALESCE(DIST_CHANNEL,SALES_CHANNEL_CD,'UNKNOWN')  AS DIST_CHANNEL_U
		,BAN AS BAN_U
		,ORDER_TYPE  AS ORDER_TYPE_U
		,SOURCE_CD  AS SOURCE_CD_U
		,POSTDATE  AS POSTDATE_U
		,BASE.PRODUCT_ID   AS PRODUCT_ID_U
		,PRODUCT_NM           AS PRODUCT_NM_U       
		,INWARD_OUTWARD_CD    AS INWARD_OUTWARD_CD_U         
		,PRODUCT_ACTION_CD    AS PRODUCT_ACTION_CD_U  
		,PROD_QTY AS PROD_QTY_U
		FROM
		( -- LEGS
			SELECT  
			 PROPERTY_ID 
			,ADDRESS
			,UNIT
			,REGION
			,PROPERTY_NAME
			,NATIONAL_IND  AS  NATIONAL_IND_U
			,DWELLING_TYPE  AS DWELLING_TYPE_U
			,AM
			,AE_ASSIGNED
			,PORTFOLIO_NAME
			,PR_STATUS
			,C_STATUS
			,MARKET_AREA
			,DISTRIBUTION_AREA
			,CHK.EVENT_ID
			,SONBR
			,DISC_RSN_CD_U
			,DISC_RSN_U   
			,SALES_CODE
			,SALES_CHANNEL_CD
			,BAN
			,ORDER_TYPE
			,SOURCE_CD
			,POSTDATE
			,PRODUCT_ID   
			,INWARD_OUTWARD_CD             
			,PRODUCT_ACTION_CD             
			,PROD_QTY  
			,CITY_NM  
			,STATE   
			FROM
			( -- CHK
				SELECT 
				 S.PROPERTY_ID 
				,S.ADDRESS
				,UNIT
				,SC.REGION
				,SC.PROPERTY_NAME
				,SC.NATIONAL_IND
				,SC.DWELLING_TYPE
				,SC.AM
				,SC.AE_ASSIGNED
				,SC.PORTFOLIO_NAME
				,SC.PR_STATUS
				,SC.C_STATUS
				,SC.MARKET_AREA
				,' ' AS DISTRIBUTION_AREA
				,E.RAAID
				,E.EVENT_ID
				,E.SONBR
				,DISC_RSN_CD_U
				,DISC_RSN_U   
				,SALES_CODE
				,SALES_CHANNEL_CD
				,BAN
				,ORDER_TYPE
				,SOURCE_CD
				,E.POSTDATE
				,E.CONTACT_NM
				,E.CITY_NM
				,E.STATE
				,E.ZIP_CD
				FROM 
				SMARTMOVES.TXSM012_SMOVES_ADDRESSES  
				S
				,SMARTMOVES.TXSM101_SO_SCORE 	
				SC
				,(	-- E		  		
		  			SELECT 
		  			 RAAID
					,EVENT_ID
					,SONBR
					,DISC_RSN_CD_U
					,DISC_RSN_U   
					,SALES_CODE
					,SALES_CHANNEL_CD
					,BAN
					,ORDER_TYPE
					,SOURCE_CD
					,POSTDATE
					,CONTACT_NM
					,CITY_NM
					,ADDRESS
					,UNIT
					,STATE
					,ZIP_CD					
					FROM
					( -- BASE
						SELECT	
						 REPOSITORY_AFFILIATE_ACCT_ID RAAID
						,EVENT_ID
						,SONBR
						,DISC_RSN_CD_U
						,DISC_RSN_U   
						,SALES_CODE
						,SALES_CHANNEL_CD
						,BAN
						,ORDER_TYPE
						,SOURCE_CD
						,POSTDATE
						,ADDRESS_ID
						,CONTACT_NM
						,CITY_NM
						,PRIMARY_ADDRESS_TXT
						,SECONDARY_ADDRESS_TXT AS  UNIT
						,TRIM(C.SECONDARY_ADDRESS_TXT) (NAMED UNIT_ADD)     
				         ,CASE WHEN C.STREET_DIRECTION_PREFIX_TXT > ' '      
				             THEN TRIM(C.STREET_DIRECTION_PREFIX_TXT) ||' '||
				                  TRIM(C.STREET_NM)                          
				             ELSE C.STREET_NM                                
				             END (NAMED ADR1)                                
				         ,CASE WHEN C.ADDRESS_HOUSE_NBR > ' '                
				             THEN TRIM(C.ADDRESS_HOUSE_NBR) ||' '|| TRIM(ADR1)    
				             ELSE ADR1                                            
				             END (NAMED ADR2)                                     
				         ,CASE WHEN C.ADDRESS_SUFFIX_TYPE_CD > ' '                
				             THEN TRIM(ADR2) ||' '|| TRIM(C.ADDRESS_SUFFIX_TYPE_CD)
				             ELSE ADR2                                            
				             END (NAMED ADR3)                                     
				         ,CASE WHEN C.STREET_DIRECTION_SUFFIX_TXT > ' '           
				             THEN TRIM(ADR3) ||' '||                              
				                  TRIM(C.STREET_DIRECTION_SUFFIX_TXT)             
				             ELSE ADR3                                            
				             END (NAMED ADDRESS)      
						,C.ZIP_CD
						,C.TERRITORY_CD AS STATE
					    FROM  
					 	ENTERPRISE_RETAIL_VIEWS.VCCG559_ACCOUNT_CNTCT_GEO_ADDR 
						C  
						,
						( --S
							SELECT 
  							 H205.REPOSITORY_AFFILIATE_ACCT_ID  RAAID  
					 		,H205.EVENT_ID
							,H205.SERVICE_ORDER_NBR        		SONBR
							,DISC_RSN_CD_U
							,DISC_RSN_U     
							,O.BAN
							,H205.EVENT_POSTED_DT    			POSTDATE
							,H205.EVENT_COMPLETION_DT    		CMPLDATE 
							,H205.EVENT_CLASS_CD				ORDER_TYPE
							,H205.DATA_SOURCE_CD				SOURCE_CD
							,SUBSTR(H205.ORIGINATING_SALES_CD,1,7) AS SALES_CODE     
							,CASE WHEN O.SALES_CHANNEL_CD   = 'WEB' THEN 'ONLINE' ELSE O.SALES_CHANNEL_CD END AS SALES_CHANNEL_CD            
							FROM
							 ENTERPRISE_RETAIL_VIEWS.VCCH205_SERVICE_ORDER_EVENT H205
							 ,( --O
							 		SELECT 
							 		 O.ORDER_ID	
									,O.SERVICE_ORDER_CD
									,O.BAN
									,SALES_CHANNEL_CD
									,DISC_RSN_CD_U     
									,DISC_RSN_U
								  	FROM	  TELCO_UNREG_RETAIL_VIEWS.VCTV800_ORDER	
								  	O					
									LEFT OUTER JOIN 
									( --D
										SELECT 
										 A.ORDER_ID
										,A.ORDER_ACTION_TYPE_CD
										,A.ORDER_ACTION_REASON_CD   DISC_RSN_CD_U
										,C.ORDER_ACTION_REASON_DESC DISC_RSN_U
										FROM
										TELCO_UNREG_RETAIL_VIEWS.VCTV801_ORDER_ACTION  	 
										A 
										,TELCO_UNREG_RETAIL_VIEWS.VCTV804_ORDER_ACTION_RSN_DESC  
										C
										WHERE  
										A.ORDER_ACTION_REASON_CD = C.ORDER_ACTION_REASON_CD
										AND	A.ORDER_ACTION_TYPE_CD = C.ORDER_ACTION_TYPE_CD
										AND A.ORDER_ACTION_REVISION_TYPE_CD = C.ORDER_ACTION_REVISION_TYPE_CD
										AND	A.CURRENT_ORDER_ACTION_STATUS_CD = 'DO'
										AND A.ORDER_ACTION_TYPE_CD IN ('CH', 'CE') 
								)
								D
							   	ON O.ORDER_ID = D.ORDER_ID
							)
							O	---------------------------------------------------------------------------------------------------------------------------------------------------------------												
							WHERE   
									EXTRACT(MONTH FROM EVENT_POSTED_DT) = EXTRACT(MONTH FROM (DATE-1))
									AND EXTRACT(YEAR FROM EVENT_POSTED_DT) = EXTRACT(YEAR FROM (DATE-1))

							
							AND H205.DATA_SOURCE_CD IN (150)
							AND O.ORDER_ID = SONBR
							AND RAAID > 0
							GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12   
						) 
						S
						WHERE C.REPOSITORY_AFFILIATE_ACCT_ID = S.RAAID
                        AND C.ZIP_CD <> ' '
						AND C.ADDRESS_USAGE_CD = 'S' 
					) 
					BASE
					GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17
				) 
				E
				WHERE TRIM(S.ADDRESS) = TRIM(E.ADDRESS)
				AND S.ZIP = E.ZIP_CD
				AND S.PROPERTY_ID = SC.PROPERTY_ID
				AND (SC.PR_STATUS = 'ACTIVE' AND C_STATUS = 'ACTIVE' ) 
				UNION
				SELECT 
				 S.PROPERTY_ID 
				,TRIM(S.SERVICE_BUILDING_NBR) || ' ' || TRIM(S.ADDRESS)  AS  STREET_ADDRESS
				,'' UNIT
				,SC.REGION
				,SC.PROPERTY_NAME
				,SC.NATIONAL_IND
				,SC.DWELLING_TYPE
				,SC.AM
				,SC.AE_ASSIGNED
				,SC.PORTFOLIO_NAME
				,SC.PR_STATUS
				,SC.C_STATUS
				,SC.MARKET_AREA
				,' ' AS DISTRIBUTION_AREA
				,E.RAAID
				,E.EVENT_ID
				,E.SONBR
				,DISC_RSN_CD_U
				,DISC_RSN_U   
				,SALES_CODE
				,SALES_CHANNEL_CD
				,BAN
				,ORDER_TYPE
				,SOURCE_CD
				,E.POSTDATE
				,E.CONTACT_NM
				,E.CITY_NM
				,E.STATE
				,E.ZIP_CD
				FROM SMARTMOVES.TXSM012_SMOVES_ADDRESSES  
				S
				,SMARTMOVES.TXSM101_SO_SCORE  	
				SC
				,(
						SELECT RAAID
						,EVENT_ID
						,SONBR
						,DISC_RSN_CD_U
						,DISC_RSN_U   
						,SALES_CODE
						,SALES_CHANNEL_CD
						,BAN
						,ORDER_TYPE
						,SOURCE_CD
						,POSTDATE
						,CONTACT_NM
						,CITY_NM
						,ADDRESS
						,STATE
						,ZIP_CD	
						FROM
						(
						SELECT
						 REPOSITORY_AFFILIATE_ACCT_ID RAAID
						,EVENT_ID
						,SONBR
						,DISC_RSN_CD_U
						,DISC_RSN_U   
						,SALES_CODE
						,SALES_CHANNEL_CD
						,BAN
						,ORDER_TYPE
						,SOURCE_CD
						,POSTDATE
						,ADDRESS_ID
						,CONTACT_NM
						,CITY_NM
						,PRIMARY_ADDRESS_TXT
						,TRIM(C.SECONDARY_ADDRESS_TXT) (NAMED UNIT_ADD)     
						,CASE WHEN C.STREET_DIRECTION_PREFIX_TXT > ' '      
						THEN TRIM(C.STREET_DIRECTION_PREFIX_TXT) ||' '||
						TRIM(C.STREET_NM)                          
						ELSE C.STREET_NM                                
						END (NAMED ADR1)                                
						,CASE WHEN C.ADDRESS_HOUSE_NBR > ' '                
						THEN TRIM(C.ADDRESS_HOUSE_NBR) ||' '|| TRIM(ADR1)    
						ELSE ADR1                                            
						END (NAMED ADR2)                                     
						,CASE WHEN C.ADDRESS_SUFFIX_TYPE_CD > ' '                
						THEN TRIM(ADR2) ||' '|| TRIM(C.ADDRESS_SUFFIX_TYPE_CD)
						ELSE ADR2                                            
						END (NAMED ADR3)                                     
						,CASE WHEN C.STREET_DIRECTION_SUFFIX_TXT > ' '           
						THEN TRIM(ADR3) ||' '||                              
						TRIM(C.STREET_DIRECTION_SUFFIX_TXT)             
						ELSE ADR3                                            
						END (NAMED ADDRESS)      
						,C.ZIP_CD
						,C.TERRITORY_CD AS STATE
						FROM  
						ENTERPRISE_RETAIL_VIEWS.VCCG559_ACCOUNT_CNTCT_GEO_ADDR 
						C
						,( -- S
								SELECT 
								 H205.REPOSITORY_AFFILIATE_ACCT_ID  RAAID  
								,H205.EVENT_ID
								,H205.SERVICE_ORDER_NBR        		SONBR
								,DISC_RSN_CD_U
								,DISC_RSN_U     
								,O.BAN
								,H205.EVENT_POSTED_DT    			POSTDATE
								,H205.EVENT_COMPLETION_DT    		CMPLDATE 
								,H205.EVENT_CLASS_CD				ORDER_TYPE
								,H205.DATA_SOURCE_CD				SOURCE_CD
								,SUBSTR(H205.ORIGINATING_SALES_CD,1,7) AS SALES_CODE     
								,CASE WHEN O.SALES_CHANNEL_CD   = 'WEB' THEN 'ONLINE' ELSE O.SALES_CHANNEL_CD END AS SALES_CHANNEL_CD            
								FROM
								ENTERPRISE_RETAIL_VIEWS.VCCH205_SERVICE_ORDER_EVENT H205
								,( --0
										SELECT 
										 O.ORDER_ID	
										,O.SERVICE_ORDER_CD
										,O.BAN
										,SALES_CHANNEL_CD
										,DISC_RSN_CD_U     
										,DISC_RSN_U
										FROM	  TELCO_UNREG_RETAIL_VIEWS.VCTV800_ORDER	
										O					
										LEFT OUTER JOIN 
										(
												SELECT 
												 A.ORDER_ID
												,A.ORDER_ACTION_TYPE_CD
												,A.ORDER_ACTION_REASON_CD   DISC_RSN_CD_U
												,C.ORDER_ACTION_REASON_DESC DISC_RSN_U
												FROM
												TELCO_UNREG_RETAIL_VIEWS.VCTV801_ORDER_ACTION  	 
												A 
												,TELCO_UNREG_RETAIL_VIEWS.VCTV804_ORDER_ACTION_RSN_DESC  
												C
												WHERE  A.ORDER_ACTION_REASON_CD = C.ORDER_ACTION_REASON_CD
												AND	A.ORDER_ACTION_TYPE_CD = C.ORDER_ACTION_TYPE_CD
												AND A.ORDER_ACTION_REVISION_TYPE_CD = C.ORDER_ACTION_REVISION_TYPE_CD
												AND	A.CURRENT_ORDER_ACTION_STATUS_CD = 'DO'
												AND A.ORDER_ACTION_TYPE_CD IN ('CH', 'CE') 
										) 
										D
										ON O.ORDER_ID = D.ORDER_ID
								) 	
								O
								WHERE   
									EXTRACT(MONTH FROM EVENT_POSTED_DT) = EXTRACT(MONTH FROM (DATE-1))
									AND EXTRACT(YEAR FROM EVENT_POSTED_DT) = EXTRACT(YEAR FROM (DATE-1))
								------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
								AND H205.DATA_SOURCE_CD IN (150)
								AND O.ORDER_ID = SONBR
								AND RAAID > 0
								GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12    
						) 
						S
						WHERE C.REPOSITORY_AFFILIATE_ACCT_ID = S.RAAID
						AND C.ZIP_CD <> ' '
						AND C.ADDRESS_USAGE_CD = 'S' ) BASE
						GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16 
				) 
				E
				WHERE TRIM(STREET_ADDRESS) = TRIM(E.ADDRESS)
				AND S.SERVICE_CITY_NM = E.CITY_NM
				AND S.SERVICE_CITY_NM <> ' '
				AND E.STATE = 'CT' 
				AND S.PROPERTY_ID = SC.PROPERTY_ID
				AND (SC.PR_STATUS = 'ACTIVE' AND C_STATUS = 'ACTIVE' )						
			) 
			CHK
			LEFT OUTER JOIN 
			( --ACT
				SELECT 
				 D.EVENT_ID
				,D.PRODUCT_ID                    
				,D.INWARD_OUTWARD_CD             
				,CASE WHEN INWARD_OUTWARD_CD = 1 THEN 'I' ELSE 'O' END AS PRODUCT_ACTION_CD             
				,SUM(D.PRODUCT_QTY)    PROD_QTY                              
				FROM 
				ENTERPRISE_RETAIL_VIEWS.VCCH207_SVC_ORD_PROD_EVENT  
				D
				WHERE  D.DATA_SOURCE_CD IN (150)
				AND D.INWARD_OUTWARD_CD  IN (1,4)
				AND D.PRODUCT_ID IN
						(
							10005132	,--U-VERSE VIDEO - BASIC PACKAGE  - U450
							10005022	,--U-VERSE VIDEO - BASIC PACKAGE - 100 CHNLS
							10005020	,--U-VERSE VIDEO - BASIC PACKAGE - 200 CHNLS
							10005021	,--U-VERSE VIDEO - BASIC PACKAGE - 300 CHNLS
							10005023	,--U-VERSE VIDEO - BASIC PACKAGE - 400 CHNLS
							10005134	,--U-VERSE VIDEO - BASIC PACKAGE - BH200
							10005139	,--U-VERSE VIDEO - BASIC PACKAGE - BP200
							10005024	,--U-VERSE VIDEO - BASIC PACKAGE - U - FAMILY
							10005137	,--U-VERSE VIDEO - BASIC PACKAGE - U200 LATINO
							10005140	,--U-VERSE VIDEO - BASIC PACKAGE - UBASIC
							--10005138	,--U-VERSE VIDEO - BASIC PACKAGE - UCOMP
							--10005136	,--U-VERSE VIDEO - BASIC PACKAGE - UDEMO
							10005133	,--U-VERSE VIDEO - BASIC PACKAGE - UGOV
							10005135	,--U-VERSE VIDEO - BASIC PACKAGE - UU300
							10005150	,--U-VERSE HSIA - BASIC
							10005148	,--U-VERSE HSIA - BASIC -STATIC
							10005006	,--U-VERSE HSIA - ELITE-DYNAMIC
							10005009	,--U-VERSE HSIA - ELITE-STATIC
							10005112	,--U-VERSE HSIA - MAX -DYNAMIC
							10005113	,--U-VERSE HSIA - MAX -STATIC
							10005151	,--U-VERSE HSIA - MAX TURBO
							10005149	,--U-VERSE HSIA - MAX TURBO -STATIC
							10005116	,--U-VERSE HSIA - MAX18 -DYNAMIC
							10005117	,--U-VERSE HSIA - MAX18 -STATIC
							10005005	,--U-VERSE HSIA - PRO-DYNAMIC
							10005008	,--U-VERSE HSIA - PRO-STATIC
							10005004	,--U-VERSE HSIA-EXPRESS-DYNAMIC
							10005007	,--U-VERSE HSIA-EXPRESS-STATIC
							10005108	,--AT&T U-VERSE VOICE 1000
							10005157	,--AT&T U-VERSE VOICE 250
							10005107	,--AT&T U-VERSE VOICE 500
							10005109	,--AT&T U-VERSE VOICE MOVEMENT
							10005106	--AT&T U-VERSE VOICE UNLIMITED
						 )				
						 GROUP BY 1,2,3,4
			) 
			ACT
			ON CHK.EVENT_ID = ACT.EVENT_ID
		)
		BASE
		LEFT OUTER JOIN	
		( --P
			SELECT 
			 PRODUCT_ID
			,PRODUCT_NM
			FROM
			ENTERPRISE_RETAIL_VIEWS.VCCR200_PRODUCT   
			WHERE PRODUCT_LAST_EFFECTIVE_DT > DATE
			AND PRODUCT_ID > 0
			GROUP BY 1,2
		)  
		P
		ON BASE.PRODUCT_ID = P.PRODUCT_ID
		LEFT OUTER JOIN	
		( --S
			SELECT 
			 SALES_CODE
			,CHANNEL  AS DIST_CHANNEL
			FROM
			MIS.SALES_CHANNEL_LOOKUP
			GROUP BY 1,2
		) 
		S
		ON BASE.SALES_CODE = S.SALES_CODE					
		LEFT OUTER JOIN
/*   ADDED 9/9/09 PER MIST REQUEST  126821   */
		(	--W
			SELECT 
			PROPERTY_ID
			,WIRE_CENTER
			FROM SMARTMOVES.TXSM011_SMOVES_PROPERTIES    
			GROUP BY 1,2
		) 
		W
		ON BASE.PROPERTY_ID = W.PROPERTY_ID						
		WHERE PRODUCT_ACTION_CD IN ('I','O')
		GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34
	) 
	TOT					
	/*  ADDED PER  MIST  131152: Determine/Develop Process for CLLI-DA Tracking for ACC- Uverse    */
	LEFT OUTER JOIN
	(SELECT WCDA
	,SEGMENT AS SEGMENT_U
	FROM SPECIAL_TABLES.DG8331_ABCD_SEGS
	GROUP BY 1,2) SEG
	ON TOT.WCDA_U = SEG.WCDA
